﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Collections.ObjectModel;
using DefectDBTypes;
using System.ComponentModel;
using Defecter.Gui.Access;
using System.Diagnostics;



namespace Defector
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {

        public SqlConnection con;

        public int userID;
        public String firstName;
        public String lastName;

        public MainWindow()
        {
            InitializeComponent();
            DataContext = this;           //needed for graph 
        }

        private void MenuItem_Click(object sender, RoutedEventArgs e)
        {
            MenuItem mi = e.Source as MenuItem;
            switch (mi.Name)
            {

                case "Exit":
                    {
                        Environment.Exit(0);
                        break;
                    }
                case "logOff":
                    {
                        Login login = new Login();
                        login.Show();
                        Application.Current.MainWindow = login;
                        this.Close();
                        break;
                    }
                case "submitDefect":
                    {
                        Submit submit = new Submit();
                        submit.con = con;
                        submit.start();
                        submit.Show();
                        break;
                    }
                case "help":
                    {
                        Help help = new Help();
                       
                        help.Show();
                        break;
                    }
                case "newUser":
                    {
                        newUser newuser = new newUser();
                        newuser.con = con;
                        newuser.Show();
                        break;
                    }
                case "addSoftware":
                    {
                        addSoftware newsoftware = new addSoftware();
                        newsoftware.con = con;
                        newsoftware.start();
                        newsoftware.Show();
                        break;
                    }
                case "addCompany":
                    {
                        addCompany newCompany = new addCompany();
                        newCompany.con = con;
                        newCompany.Show();
                        break;
                    }
                case "Stats":
                {
                   Stats s = new Stats();
                   s.con = con;
                   s.DevOpen1();
                   s.DevClosed1();
                   s.DevTotal1();
                   s.UnAssigned();
                   s.Assigned();
                   s.New();
                   s.isCritical();
                   s.isHigh();
                   s.islow();
                   s.isMed();
                  /* s.TesterOpen1();
                   s.TesterClosed1();
                   s.TesterTotal1();*/
                   s.Show();
                   break;
                }
            }
        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {

            bottomStatusBar.Content += firstName + " " + lastName;

            //Load data into your defects grid
            SqlCommand cmd = new SqlCommand("SELECT dbo.Trouble_Report.Trouble_Report_Id AS ID, dbo.Trouble_Report.Priority, dbo.Trouble_Report.Priority, CONVERT(CHAR(12), dbo.Trouble_Report.Date_Submitted, 101) AS dateSubmitted, dbo.Softwares.Software_Name AS Software, dbo.Trouble_Report.Headline, dbo.Trouble_Report.Description FROM dbo.Trouble_Report INNER JOIN dbo.People ON dbo.Trouble_Report.PersonId = dbo.People.Id INNER JOIN dbo.Softwares ON dbo.Trouble_Report.SoftwareSoftware_Id = dbo.Softwares.Software_Id WHERE (dbo.Trouble_Report.Status = 1) AND (dbo.People.Id = @userID )", con);
            cmd.Parameters.AddWithValue("@userID", userID);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet yourDefects = new DataSet();
            adapter.Fill(yourDefects);
            yourDefectsDataGrid.DataContext = yourDefects.Tables[0].DefaultView;

            //Load data into open defects grid
            cmd = new SqlCommand("SELECT dbo.Trouble_Report.Trouble_Report_Id AS ID, dbo.Trouble_Report.Priority, dbo.Trouble_Report.Priority, CONVERT(CHAR(12), dbo.Trouble_Report.Date_Submitted, 101) AS dateSubmitted, dbo.People.First_Name + ' ' + dbo.People.Last_Name AS Owner, dbo.Softwares.Software_Name AS Software, dbo.Trouble_Report.Headline, dbo.Trouble_Report.Description FROM dbo.Trouble_Report INNER JOIN dbo.People ON dbo.Trouble_Report.PersonId = dbo.People.Id INNER JOIN dbo.Softwares ON dbo.Trouble_Report.SoftwareSoftware_Id = dbo.Softwares.Software_Id WHERE (dbo.Trouble_Report.Status = 1)", con);
            adapter = new SqlDataAdapter(cmd);
            DataSet openDefects = new DataSet();
            adapter.Fill(openDefects);
            openDefectsData.DataContext = openDefects.Tables[0].DefaultView;

            //Load data into closed defects grid
            cmd = new SqlCommand("SELECT dbo.Trouble_Report.Trouble_Report_Id AS ID, dbo.Trouble_Report.Priority, CONVERT(CHAR(12), dbo.Trouble_Report.Date_Completed, 101) AS dateCompleted, dbo.People.First_Name + ' ' + dbo.People.Last_Name AS Owner, dbo.Softwares.Software_Name AS Software, dbo.Trouble_Report.Headline, dbo.Trouble_Report.Description FROM dbo.Trouble_Report INNER JOIN dbo.People ON dbo.Trouble_Report.PersonId = dbo.People.Id INNER JOIN dbo.Softwares ON dbo.Trouble_Report.SoftwareSoftware_Id = dbo.Softwares.Software_Id WHERE (dbo.Trouble_Report.Status = 0)", con);
            adapter = new SqlDataAdapter(cmd);
            DataSet closedDefects = new DataSet();
            adapter.Fill(closedDefects);
            closedDefectsData.DataContext = closedDefects.Tables[0].DefaultView;

            //Load data into new defects grid
            cmd = new SqlCommand("SELECT dbo.Trouble_Report.Trouble_Report_Id AS ID, dbo.Trouble_Report.Priority, dbo.Trouble_Report.Priority, CONVERT(CHAR(12), dbo.Trouble_Report.Date_Submitted, 101) AS dateSubmitted, dbo.Softwares.Software_Name AS Software, dbo.Trouble_Report.Headline, dbo.Trouble_Report.Description FROM dbo.Trouble_Report INNER JOIN dbo.Softwares ON dbo.Trouble_Report.SoftwareSoftware_Id = dbo.Softwares.Software_Id WHERE (dbo.Trouble_Report.Status IS NULL)", con);
            adapter = new SqlDataAdapter(cmd);
            DataSet newDefects = new DataSet();
            adapter.Fill(newDefects);
            newDefectsDataGrid.DataContext = newDefects.Tables[0].DefaultView;

            //Load data into inbox message grid
            cmd = new SqlCommand("SELECT dbo.Messages.Message_Id as id, dbo.Messages.Sender AS sender, dbo.Messages.Subject AS subject, dbo.Messages.Content as content, dbo.Messages.isNew AS isNew, dbo.Messages.isTrash AS isTrash FROM dbo.Messages WHERE (dbo.Messages.PersonId = @userID ) AND (isTrash = 0) ORDER BY id DESC", con);
            cmd.Parameters.AddWithValue("@userID", userID);
            adapter = new SqlDataAdapter(cmd);
            DataSet InboxMessages = new DataSet();
            adapter.Fill(InboxMessages);
            InboxGrid.DataContext = InboxMessages;

            //Load data into trash message grid
            cmd = new SqlCommand("SELECT dbo.Messages.Message_Id as id, dbo.Messages.Sender AS sender, dbo.Messages.Subject AS subject, dbo.Messages.Content as content, dbo.Messages.isNew AS isNew, dbo.Messages.isTrash AS isTrash FROM dbo.Messages WHERE (dbo.Messages.PersonId = @userID ) AND (isTrash = 1) ORDER BY id DESC", con);
            cmd.Parameters.AddWithValue("@userID", userID);
            adapter = new SqlDataAdapter(cmd);
            DataSet TrashMessages = new DataSet();
            adapter.Fill(TrashMessages);
            TrashGrid.DataContext = TrashMessages;

            loadSoftwareData();

        }

        private void newOrdersRefresh_Click(object sender, RoutedEventArgs e)
        {
            //Load data into new defects grid
            SqlCommand cmd = new SqlCommand("SELECT dbo.Trouble_Report.Trouble_Report_Id AS ID, dbo.Trouble_Report.Priority, dbo.Trouble_Report.Priority, CONVERT(CHAR(12), dbo.Trouble_Report.Date_Submitted, 101) AS dateSubmitted, dbo.Softwares.Software_Name AS Software, dbo.Trouble_Report.Headline, dbo.Trouble_Report.Description FROM dbo.Trouble_Report INNER JOIN dbo.Softwares ON dbo.Trouble_Report.SoftwareSoftware_Id = dbo.Softwares.Software_Id WHERE (dbo.Trouble_Report.Status IS NULL)", con);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet newDefects = new DataSet();
            adapter.Fill(newDefects);
            newDefectsDataGrid.DataContext = newDefects.Tables[0].DefaultView;
        }

        private void openOrdersRefresh_Click(object sender, RoutedEventArgs e)
        {
            //Load data into open defects grid
            SqlCommand cmd = new SqlCommand("SELECT dbo.Trouble_Report.Trouble_Report_Id AS ID, dbo.Trouble_Report.Priority, CONVERT(CHAR(12), dbo.Trouble_Report.Date_Submitted, 101) AS dateSubmitted, dbo.People.First_Name + ' ' + dbo.People.Last_Name AS Owner, dbo.Softwares.Software_Name AS Software, dbo.Trouble_Report.Headline, dbo.Trouble_Report.Description FROM         dbo.Trouble_Report INNER JOIN dbo.People ON dbo.Trouble_Report.PersonId = dbo.People.Id INNER JOIN dbo.Softwares ON dbo.Trouble_Report.SoftwareSoftware_Id = dbo.Softwares.Software_Id WHERE (dbo.Trouble_Report.Status = 1)", con);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet openDefects = new DataSet();
            adapter.Fill(openDefects);
            openDefectsData.DataContext = openDefects.Tables[0].DefaultView;
        }

        private void closedOrdersRefresh_Click(object sender, RoutedEventArgs e)
        {
            //Load data into closed defects grid
            SqlCommand cmd = new SqlCommand("SELECT dbo.Trouble_Report.Trouble_Report_Id AS ID, dbo.Trouble_Report.Priority, CONVERT(CHAR(12), dbo.Trouble_Report.Date_Completed, 101) AS dateCompleted, dbo.People.First_Name + ' ' + dbo.People.Last_Name AS Owner, dbo.Softwares.Software_Name AS Software, dbo.Trouble_Report.Headline, dbo.Trouble_Report.Description FROM         dbo.Trouble_Report INNER JOIN dbo.People ON dbo.Trouble_Report.PersonId = dbo.People.Id INNER JOIN dbo.Softwares ON dbo.Trouble_Report.SoftwareSoftware_Id = dbo.Softwares.Software_Id WHERE (dbo.Trouble_Report.Status = 0)", con);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet closedDefects = new DataSet();
            adapter.Fill(closedDefects);
            closedDefectsData.DataContext = closedDefects.Tables[0].DefaultView;
        }

        private void yourDefectsRefresh_Click(object sender, RoutedEventArgs e)
        {
            //Load data into your defects grid
            SqlCommand cmd = new SqlCommand("SELECT dbo.Trouble_Report.Trouble_Report_Id AS ID, dbo.Trouble_Report.Priority,CONVERT(CHAR(12), dbo.Trouble_Report.Date_Submitted, 101) AS dateSubmitted, dbo.Softwares.Software_Name AS Software, dbo.Trouble_Report.Headline, dbo.Trouble_Report.Description FROM dbo.Trouble_Report INNER JOIN dbo.People ON dbo.Trouble_Report.PersonId = dbo.People.Id INNER JOIN dbo.Softwares ON dbo.Trouble_Report.SoftwareSoftware_Id = dbo.Softwares.Software_Id WHERE (dbo.Trouble_Report.Status = 1) AND (dbo.People.Id = @userID )", con);
            cmd.Parameters.AddWithValue("@userID", userID);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet yourDefects = new DataSet();
            adapter.Fill(yourDefects);
            yourDefectsDataGrid.DataContext = yourDefects.Tables[0].DefaultView;
        }

        //Pop up a report window when the user double clicks the datagridview 
        private void DataGrid_MouseDoubleClick(object sender, System.Windows.Input.MouseButtonEventArgs e)
        {
            IInputElement element = e.MouseDevice.DirectlyOver;
            if (element != null && element is FrameworkElement)
            {
                if (((FrameworkElement)element).Parent is DataGridCell)
                {
                    var grid = sender as DataGrid;
                    if (grid != null && grid.SelectedItems != null && grid.SelectedItems.Count == 1)
                    {
                        var rowView = grid.SelectedItem as DataRowView;
                        if (rowView != null)
                        {
                            DataRow row = rowView.Row;
                            Report report = new Report();
                            report.Title = "Defect ID: " + row.ItemArray[0].ToString();
                            report.reportNumber = Convert.ToInt32(row.ItemArray[0].ToString());
                            report.con = con;
                            report.currentUserID = userID;
                            report.Start();
                            report.Show();
                        }
                    }
                }
            }
        }


        private void OpenMessageWindow(object sender, System.Windows.Input.MouseButtonEventArgs e)
        {
            IInputElement element = e.MouseDevice.DirectlyOver;
            if (element != null && element is FrameworkElement)
            {
                if (((FrameworkElement)element).Parent is DataGridCell)
                {
                    var grid = sender as DataGrid;
                    if (grid != null && grid.SelectedItems != null && grid.SelectedItems.Count == 1)
                    {
                        var rowView = grid.SelectedItem as DataRowView;
                        if (rowView != null)
                        {
                            DataRow row = rowView.Row;
                            Mail mail = new Mail(row);
                            mail.setCon(con);
                            mail.setData();
                            mail.Show();
                        }
                    }
                }
            }
        }
        

        private void btnGlossarySearch_Click(object sender, RoutedEventArgs e)
        {
            string searchTerm = txtSearchTerm.Text.Trim();
             
            if (ckbPartial.IsChecked == true)
            {
                string newSearchTerm = "%" + searchTerm + "%";
                searchTerm = newSearchTerm;
            }

            string query;

            if (cmbSearchBy.SelectedIndex == 0)
            {
                query = "SELECT dbo.Glossary.term AS term, dbo.Glossary.definition AS definition FROM dbo.GLOSSARY WHERE term LIKE @searchTerm;";
            }
            else
            {
                query = "SELECT dbo.Glossary.term AS term, dbo.Glossary.definition AS definition FROM dbo.GLOSSARY WHERE definition LIKE @searchTerm;";
            }

            SqlCommand glossarySearchCommand = new SqlCommand(query, con);
            glossarySearchCommand.Parameters.AddWithValue("@searchTerm", searchTerm);
            SqlDataAdapter glossaryDataAdapter = new SqlDataAdapter(glossarySearchCommand);
            DataSet glossaryDataSet = new DataSet();
            glossaryDataAdapter.Fill(glossaryDataSet);
            glossaryGrid.DataContext = glossaryDataSet;

        }

        private void txtSearchTerm_TextChanged(object sender, TextChangedEventArgs e)
        {
            if (String.IsNullOrEmpty(txtSearchTerm.Text.Trim()))
            {
                btnGlossarySearch.IsEnabled = false;
            }
            else
            {
                btnGlossarySearch.IsEnabled = true;
            }

        }

        private void ComposeMail(object sender, RoutedEventArgs e)
        {
            Compose compose = new Compose(firstName, lastName);
            compose.setCon(con);
            compose.setData();
        }

        private void MessageRefreshClick(object sender, RoutedEventArgs e)
        {
            MessageRefresh();
        }

        private void MessageRefresh()
        {
            //refresh inbox
            SqlCommand cmd = new SqlCommand("SELECT dbo.Messages.Message_Id as id, dbo.Messages.Sender AS sender, dbo.Messages.Subject AS subject, dbo.Messages.Content as content, dbo.Messages.isNew AS isNew, dbo.Messages.isTrash AS isTrash FROM dbo.Messages WHERE (dbo.Messages.PersonId = @userID ) AND (isTrash = 0) ORDER BY id DESC", con);
            cmd.Parameters.AddWithValue("@userID", userID);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet InboxMessages = new DataSet();
            adapter.Fill(InboxMessages);
            InboxGrid.DataContext = InboxMessages;

            //refresh trash
            cmd = new SqlCommand("SELECT dbo.Messages.Message_Id as id, dbo.Messages.Sender AS sender, dbo.Messages.Subject AS subject, dbo.Messages.Content as content, dbo.Messages.isNew AS isNew, dbo.Messages.isTrash AS isTrash FROM dbo.Messages WHERE (dbo.Messages.PersonId =  @userID ) AND (isTrash = 1) ORDER BY id DESC", con);
            cmd.Parameters.AddWithValue("@userID", userID);
            adapter = new SqlDataAdapter(cmd);
            DataSet TrashMessages = new DataSet();
            adapter.Fill(TrashMessages);
            TrashGrid.DataContext = TrashMessages;
        }

        private void EmptyTrash(object sender, RoutedEventArgs e)
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("DELETE FROM dbo.Messages WHERE (dbo.Messages.PersonId = @userID) AND (dbo.Messages.isTrash = 1)", con);
                cmd.Parameters.AddWithValue("@userID", userID);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error connecting to messages.  Please try again.");
            }
            finally 
            {
                con.Close();
            }

            MessageRefresh();
        }

        //Search handling 
        private void searchButton_Click(object sender, RoutedEventArgs e)
        {
            String query;
            int arguments = 0;
            int searchID = 0;
            string priority = "";
            int status = 0;
            int softwareID = 0;
            Boolean idSet = false;
            Boolean prioritySet = false;
            Boolean statusSet = false;
            Boolean softwareSet = false;

            //Main query
            query = "SELECT dbo.Trouble_Report.Trouble_Report_Id AS ID, CONVERT(CHAR(12), dbo.Trouble_Report.Date_Submitted, 101) AS Date_Submitted, dbo.People.First_Name + ' ' + dbo.People.Last_Name AS Owner, dbo.Trouble_Report.Headline AS Headline, dbo.Trouble_Report.Description AS Description, dbo.Softwares.Software_Name AS Software_Id, dbo.Trouble_Report.Priority AS Priority, dbo.Trouble_Report.Status FROM dbo.Trouble_Report INNER JOIN dbo.Softwares ON dbo.Trouble_Report.SoftwareSoftware_Id = dbo.Softwares.Software_Id INNER JOIN dbo.People ON dbo.Trouble_Report.PersonId = dbo.People.Id";

            //If there is an id # to search
            if (idSearchBox.Text != "")
            {
                try
                {
                    searchID = Convert.ToInt32(idSearchBox.Text);
                    query += " WHERE (dbo.Trouble_Report.Trouble_Report_Id = @searchID)";
                    idSet = true;
                    arguments++;
                }
                catch (FormatException ex)
                {
                    MessageBox.Show("Error non-neumeric input!");
                }
                arguments++;
            }

            //If there is a priority parameter set
            if (searchPriority.SelectedIndex > 0)
            {
                if (searchPriority.SelectedIndex == 1)
                    priority = "Low";
                else if (searchPriority.SelectedIndex == 2)
                    priority = "Medium";
                else if (searchPriority.SelectedIndex == 3)
                    priority = "High";
                else if (searchPriority.SelectedIndex == 4)
                    priority = "Critical";

                if (arguments > 0)
                    query += " AND (dbo.Trouble_Report.Priority = @priority)";
                else
                    query += " WHERE (dbo.Trouble_Report.Priority = @priority)";
                arguments++;
                prioritySet = true;
            }

            //If there is a status parameter set
            if (statusSearch.SelectedIndex > 0)
            {
                
                if (statusSearch.SelectedIndex == 1)
                {
                    if (arguments > 0)
                        query += " AND (dbo.Trouble_Report.Status IS NULL)";
                    else
                        query += " WHERE (dbo.Trouble_Report.Status IS NULL)";
                }
                else if (statusSearch.SelectedIndex == 2)
                    status = 1;
                else if (statusSearch.SelectedIndex == 3)
                    status = 0;

                if (arguments > 0 && statusSearch.SelectedIndex > 1)
                    query += " AND (dbo.Trouble_Report.Status = @status)";
                else if (statusSearch.SelectedIndex > 1)
                    query += " WHERE (dbo.Trouble_Report.Status = @status)";
                arguments++;
                statusSet = true;
            }

            //If there is a software parameter set
            if (softwareBox.SelectedIndex > -1)
            {
                if (arguments > 0)
                    query += " AND (dbo.Trouble_Report.SoftwareSoftware_Id = @softwareID)";
                else
                    query += " WHERE (dbo.Trouble_Report.SoftwareSoftware_Id = @softwareID)";
                softwareID = (int)softwareBox.SelectedValue;
                arguments++;
                softwareSet = true;
            }

            SqlCommand cmd = new SqlCommand(query, con);
            if (idSet)
                cmd.Parameters.AddWithValue("@searchID", searchID);
            if (prioritySet)
                cmd.Parameters.AddWithValue("@priority", priority);
            if(statusSet)
                cmd.Parameters.AddWithValue("@status", status);
            if (softwareSet)
                cmd.Parameters.AddWithValue("@softwareID", softwareID);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet searchDefects = new DataSet();
            adapter.Fill(searchDefects);
            searchGrid.DataContext = searchDefects.Tables[0].DefaultView;
            arguments++; 

        }

        //Load the software into the search combo box
        public void loadSoftwareData()
        {


            DataSet ds = new DataSet();
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT * FROM Softwares";
                adapter.SelectCommand = cmd;
                adapter.Fill(ds, "Software");

                DataRow nRow = ds.Tables["Software"].NewRow();

                //Binding the data to the combobox.
                softwareBox.DataContext = ds.Tables["Software"].DefaultView;

                //To display category name
                softwareBox.DisplayMemberPath =
                   ds.Tables["Software"].Columns["Software_Name"].ToString();
                //To store the ID as hidden
                softwareBox.SelectedValuePath =
                   ds.Tables["Software"].Columns["Software_Id"].ToString();
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("An error occurred while loading software data.");
            }
        }

        //Clear the search settings
        private void clearbtn_Click(object sender, RoutedEventArgs e)
        {
            idSearchBox.Text = "";
            searchPriority.SelectedIndex = 0;
            statusSearch.SelectedIndex = 0;
            softwareBox.SelectedIndex = -1;
        }
    }
}


